{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd345e1e-8bf1-4c32-a7ee-8ec83822ac21",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import datetime\n",
    "import pandas as pd\n",
    "import pymysql\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "from joblib import dump\n",
    "\n",
    "class WeatherUtils(object):\n",
    "    \"\"\"\n",
    "    天气类\n",
    "    Args:\n",
    "        object (_type_): _description_\n",
    "    \"\"\"\n",
    "    def __init__(self):\n",
    "        self.url = 'http://v1.yiketianqi.com/api/'\n",
    "        self.date = [\n",
    "            '2024-07-01',\n",
    "            '2024-08-01',\n",
    "            '2024-09-01',\n",
    "            '2024-10-01',\n",
    "            '2024-11-01',\n",
    "            '2024-12-01',\n",
    "        ]\n",
    "\n",
    "    def get_data(self):\n",
    "        \"\"\"获取天气数据\"\"\"\n",
    "        data_list = []\n",
    "        for d in self.date:\n",
    "            conf = {\n",
    "                \"appid\": \"882469599\",  # 用自己注册后的appid\n",
    "                \"appsecret\": \"B47zIjYm\",\n",
    "                \"version\": \"history\",\n",
    "                \"year\": d[:4],\n",
    "                \"month\": d[5:7],\n",
    "                \"city\": \"南昌\"\n",
    "            }\n",
    "            res = requests.get(self.url + '?', params=conf)\n",
    "            res_data = res.json()\n",
    "            for i in res_data['data']:\n",
    "                data_list.append({\n",
    "                    'date': datetime.datetime.strptime(i['ymd'], '%Y-%m-%d'),\n",
    "                    'bwendu': i['bwendu'],\n",
    "                    'ywendu': i['ywendu'],\n",
    "                    'tianqi': i['tianqi'],\n",
    "                    'fengxiang': i['fengxiang'],\n",
    "                    'fengli': i['fengli'],\n",
    "                })\n",
    "\n",
    "        df = pd.DataFrame(data_list)\n",
    "        df.to_csv('./timing/weather.csv')\n",
    "\n",
    "\n",
    "class MysqlUtils(object):\n",
    "    \"\"\"获取数据\"\"\"\n",
    "    def __init__(self):\n",
    "        self.conn = pymysql.connect(\n",
    "            host='127.0.0.1',\n",
    "            user='root',\n",
    "            passwd='root',\n",
    "            db='scenic',\n",
    "            port=3306,\n",
    "            charset='utf8'\n",
    "        )\n",
    "\n",
    "    def is_holiday(self, data):\n",
    "        \"\"\"是否节假日\"\"\"\n",
    "        if data in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2025-01-02', '2025-01-03']:\n",
    "            return 1\n",
    "        return 0\n",
    "\n",
    "    def get_scenic_data(self):\n",
    "        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)\n",
    "        sql = \"\"\"\n",
    "        SELECT DATE(g.create_time) as date, count(*) as count\n",
    "        FROM order_user_gate_vol g\n",
    "        WHERE DATE(g.create_time) < '2025-01-01' GROUP BY date\n",
    "        \"\"\"\n",
    "        cursor.execute(sql)\n",
    "        ret = cursor.fetchall()\n",
    "        df = pd.DataFrame(ret)\n",
    "        # print(df.head())\n",
    "\n",
    "        # 合并天气数据\n",
    "        self.weather_data['date'] = pd.to_datetime(self.weather_data['date'])\n",
    "        df['date'] = pd.to_datetime(df['date'])\n",
    "        df_pivot = pd.merge(self.weather_data, df, on='date')\n",
    "\n",
    "        print(df_pivot.head())\n",
    "\n",
    "        # # print(df_pivot.head())\n",
    "        # df_pivot['dow'] = df_pivot.index.dayofweek  # 星期几（0-6）\n",
    "        # df_pivot['month'] = df_pivot.index.month  # 月份\n",
    "        # df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)\n",
    "\n",
    "        # # 对星期几和月份进行独热编码\n",
    "        # df_pivot = pd.get_dummies(df_pivot, columns=['dow','month'], dtype=int)\n",
    "        # # print(df_pivot.head())\n",
    "        # # 对小时列进行归一化\n",
    "        # hours_columns = list(range(6, 24))\n",
    "        # df_hours = df_pivot[hours_columns].copy()\n",
    "\n",
    "        # feature_columns = [col for col in df_pivot.columns if col not in hours_columns]\n",
    "        # df_feature = df_pivot[feature_columns].copy()\n",
    "\n",
    "        # scaler = MinMaxScaler()\n",
    "        # scaled_hours = scaler.fit_transform(df_hours)\n",
    "        # dump(scaler, 'MN/scaler.joblib')\n",
    "\n",
    "        # # 将归一化后的数据转化为DataFrame\n",
    "        # df_hours_scaled = pd.DataFrame(scaled_hours, columns=hours_columns, index=df_hours.index)\n",
    "\n",
    "        # # 合并\n",
    "        # df_pivot_clean = pd.concat([df_hours_scaled, df_feature], axis=1)\n",
    "        # print(df_pivot_clean.head())\n",
    "        # df_pivot_clean.to_csv('MN/scenic_data.csv', index=False)\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    wu = WeatherUtils()\n",
    "    wu.get_data()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
